package com.SQLTest;


import java.sql.*;

public class MySQL_DB {

    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test";

    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "root";

    // 数据表为自增主键 插入数据时使用 null 代替自增主键值
    private static final String SQL_INSERT = "insert into user values (null, '__Fy',17,'男','17680560657')";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn;
        Statement stmt;
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);

        // 打开链接
        System.out.println("连接数据库...");

        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        // 实例化Statement对象
        stmt = conn.createStatement();

        // insert
        int result = sqlInsert(stmt);
        if (result > 0) System.out.println("Insert success.");

        // 执行查询
        ResultSet rs = sqlQuery(stmt);
        rs.close();
        stmt.close();
        conn.close();
        System.out.println("Goodbye!");
    }

    private static int sqlInsert(Statement stmt) throws SQLException {
        return stmt.executeUpdate(SQL_INSERT);
    }

    private static ResultSet sqlQuery(Statement stmt) throws SQLException {
        String sql;
        sql = "SELECT user_name, age, sex, password FROM user where user_name='KS'";
        ResultSet rs = stmt.executeQuery(sql);
        if (!rs.next()) System.out.println("没有数据");
        // 展开结果集数据库
        while (rs.next()) {
            // 通过字段检索
            int age = rs.getInt("age");
            String user_name = rs.getString("user_name");
            String sex = rs.getString("sex");
            String password = rs.getString("password");

            // 输出数据
            System.out.println("user_name: " + user_name);
            System.out.println("age: " + age);
            System.out.println("sex: " + sex);
            System.out.println("password: " + password);
            System.out.println("\n");
        }
        // 完成后关闭
        return rs;
    }
}
